package com.eleadmin.order.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.eleadmin.common.core.config.ConfigProperties;
import com.eleadmin.common.core.utils.FileServerUtil;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.common.system.entity.User;
import com.eleadmin.common.system.service.UserRoleService;
import com.eleadmin.common.system.service.UserService;
import com.eleadmin.damon.utils.constant.UserConstant;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.DateUtil;
import com.eleadmin.damon.utils.utils.PinyinUtils;
import com.eleadmin.damon.utils.utils.StringUtil;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.ocean.biz.OceanBulkCargoBiz;
import com.eleadmin.ocean.biz.OceanBulkCargoFeeBiz;
import com.eleadmin.ocean.entity.OceanBulkCargo;
import com.eleadmin.ocean.entity.OceanBulkCargoFee;
import com.eleadmin.ocean.param.OceanBulkCargoParam;
import com.eleadmin.order.biz.*;
import com.eleadmin.order.entity.*;
import com.eleadmin.order.param.ContainerBillParam;
import com.eleadmin.order.service.CustomerService;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 客户管理服务实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-02-13 15:50:11
 */
@Service
public class CustomerServiceImpl /*extends ServiceImpl<CustomerMapper, Customer> */ implements CustomerService {

    @Autowired
    private CustomerBiz customerBiz;
    @Autowired
    private ContainerBillBiz containerBillBiz;
    @Autowired
    private OceanBulkCargoBiz oceanBulkCargoBiz;
    @Autowired
    private ContainerBillFeeBiz containerBillFeeBiz;
    @Autowired
    private OceanBulkCargoFeeBiz oceanBulkCargoFeeBiz;
    @Autowired
    private UserBiz2 userBiz;
    @Autowired
    private UserService userService;
    @Autowired
    private UserRoleService userRoleService;

    @Override
    public JsonResult list(PageResult2<T> page, Customer customer, User loginUser) throws Exception {
        return customerBiz.list(page, customer, loginUser);
    }

    @Override
    public JsonResult list(Customer customer, User loginUser) {
        return JsonResult.ok().setData(customerBiz.list(customer, loginUser));
    }

    @Override
    public JsonResult listSel(Customer customer, User loginUser) {
        return JsonResult.ok().setData(customerBiz.listSel(customer, loginUser));
    }

    @Override
    public JsonResult invoiceSel(Customer customer, User loginUser) {
        return JsonResult.ok().setData(customerBiz.invoiceSel(customer, loginUser));
    }

    @Override
    public JsonResult getById(String id) {
        return JsonResult.ok().setData(customerBiz.getById(id));
    }

    @Transactional
    @Override
    public JsonResult save(Customer customer, User loginUser) {
        customer.setPinyin(PinyinUtils.ToFirstChar(customer.getName()));
        Integer id = customerBiz.save(customer, loginUser);
        return JsonResult.ok("添加成功");
    }

    @Transactional
    @Override
    public JsonResult saveCustomer(Customer customer, User loginUser) {
        customer.setPinyin(PinyinUtils.ToFirstChar(customer.getName()));
        Integer id = customerBiz.save(customer, loginUser);
        User user = new User();
        user.setUsername(customer.getAccount());
        user.setPassword(userService.encodePassword(UserConstant.DEFAULT_PSW));
        user.setOriginPassword(UserConstant.DEFAULT_PSW);
        user.setType("货代");
        user.setNickname(customer.getName());
        user.setPhone(customer.getContactsPhone());
        user.setCustomerId(id + "");
        user.setSex("1");
        Integer userid = userBiz.save(user, loginUser);
        List<Integer> roleIds = new ArrayList<>();
        roleIds.add(17);
        userRoleService.saveBatch(userid, roleIds);
        return JsonResult.ok("添加成功");
    }

    @Transactional
    @Override
    public JsonResult saveCarTeam(Customer customer, User loginUser) {
        customer.setPinyin(PinyinUtils.ToFirstChar(customer.getName()));
        Integer id = customerBiz.save(customer, loginUser);
        User user = new User();
        user.setUsername(customer.getAccount());
        user.setPassword(userService.encodePassword(UserConstant.DEFAULT_PSW));
        user.setOriginPassword(UserConstant.DEFAULT_PSW);
        user.setType("车队");
        user.setNickname(customer.getName());
        user.setPhone(customer.getContactsPhone());
        user.setCustomerId(id + "");
        user.setSex("1");
        Integer userid = userBiz.save(user, loginUser);
        List<Integer> roleIds = new ArrayList<>();
        roleIds.add(18);
        userRoleService.saveBatch(userid, roleIds);
        return JsonResult.ok("添加成功");
    }

    @Transactional
    @Override
    public JsonResult saveBookingAgent(Customer customer, User loginUser) {
        customer.setPinyin(PinyinUtils.ToFirstChar(customer.getName()));
        Integer id = customerBiz.save(customer, loginUser);
        User user = new User();
        user.setUsername(customer.getAccount());
        user.setPassword(userService.encodePassword(UserConstant.DEFAULT_PSW));
        user.setOriginPassword(UserConstant.DEFAULT_PSW);
        user.setType("订舱代理");
        user.setNickname(customer.getName());
        user.setPhone(customer.getContactsPhone());
        user.setCustomerId(id + "");
        user.setSex("1");
        Integer userid = userBiz.save(user, loginUser);
        List<Integer> roleIds = new ArrayList<>();
        roleIds.add(19);
        userRoleService.saveBatch(userid, roleIds);
        return JsonResult.ok("添加成功");
    }

    @Transactional
    @Override
    public JsonResult saveCustomBroker(Customer customer, User loginUser) {
        customer.setPinyin(PinyinUtils.ToFirstChar(customer.getName()));
        Integer id = customerBiz.save(customer, loginUser);
        User user = new User();
        user.setUsername(customer.getAccount());
        user.setPassword(userService.encodePassword(UserConstant.DEFAULT_PSW));
        user.setOriginPassword(UserConstant.DEFAULT_PSW);
        user.setType("报关行");
        user.setNickname(customer.getName());
        user.setPhone(customer.getContactsPhone());
        user.setCustomerId(id + "");
        user.setSex("1");
        Integer userid = userBiz.save(user, loginUser);
        List<Integer> roleIds = new ArrayList<>();
        roleIds.add(19);
        userRoleService.saveBatch(userid, roleIds);
        return JsonResult.ok("添加成功");
    }

    @Transactional
    @Override
    public JsonResult examCustomer(Customer customer, User loginUser) {
        if (customerBiz.examCustomer(customer, loginUser)) {
            return JsonResult.ok("审核成功");
        }
        return JsonResult.error("审核失败");
    }

    @Transactional
    @Override
    public JsonResult update(Customer customer, User loginUser) {
        customer.setPinyin(PinyinUtils.ToFirstChar(customer.getName()));
        if (customerBiz.update(customer, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Transactional
    @Override
    public JsonResult remove(String id, User loginUser) {
        if (customerBiz.remove(id, loginUser)) {
            return JsonResult.ok("删除成功");
        }
        return JsonResult.error("删除失败");
    }


//
//    //导出客户拖车账单
//    @Transactional
//    @Override
//    public JsonResult exportsCustomerContainerBill(ContainerBill containerBill, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
//        File file = new File(getUploadDir(), "containerBill.xlsx");
//        if (file.exists()) {
//            file.delete();
//        }
//        containerBill.setDate1(containerBill.getDate1().substring(0, 10) + " 00:00");
//        containerBill.setDate2(containerBill.getDate2().substring(0, 10) + " 23:59");
//        List<ContainerBill> containerBillList = getContainerList(containerBill, loginUser);
//        if (containerBillList != null && containerBillList.size() > 0) {
//            List<String> feeNames = containerBillFeeBiz.feeNames(containerBill, loginUser);
//            if (feeNames != null && feeNames.size() > 0) {
//                exportsCustomerContainerBillExcel(containerBillList, feeNames, containerBill);
//                FileServerUtil.preview(file, true, "containerBill.xlsx", null, null, response, request);
//            } else {
//                try {
//                    response.setContentType("text/html; charset=UTF-8");
//                    response.getWriter().println("没有对应的数据哦,请再次查询!");
//                } catch (IOException e) {
//                    e.printStackTrace();
//                }
//            }
//        }
//        return JsonResult.ok("文件已导出");
//    }
//
//
//    public List<ContainerBill> getContainerList(ContainerBill containerBill, User loginUser) {
//        ContainerBillParam containerBillParam = new ContainerBillParam();
//        containerBillParam.setFactoryPackDateSearch1(containerBill.getDate1());
//        containerBillParam.setFactoryPackDateSearch2(containerBill.getDate2());
//        containerBillParam.setCustomerEq(containerBill.getCustomer());
//        List<ContainerBill> containerBillList = containerBillBiz.list(containerBillParam, loginUser);
//        if (containerBillList != null && containerBillList.size() > 0) {
//            for (int i = 0; i < containerBillList.size(); i++) {
//                ContainerBill containerBillTemp = containerBillList.get(i);
//                containerBillParam = new ContainerBillParam();
//                if (StringUtil.isNotBlank(containerBill.getType())) {
//                    containerBillParam.setType(containerBill.getType());//导出应收应付
//                }
//                if (StringUtil.isNotBlank(containerBillTemp.getId())) {
//                    containerBillParam.setId(containerBillTemp.getId());
//                }
//                if (StringUtil.isNotBlank(containerBillTemp.getApplyId())) {
//                    containerBillParam.setApplyId(containerBillTemp.getApplyId());
//                }
//                if (StringUtil.isNotBlank(containerBill.getCustomerAbbreviation())) {
//                    containerBillParam.setCustomerAbbreviation(containerBill.getCustomerAbbreviation());
//                }
//                List<ContainerBillFee> containerBillFeeList = containerBillFeeBiz.listForExport(containerBillParam, loginUser);
//                containerBillTemp.setContainerBillFeeList(containerBillFeeList);
//            }
//        }
//        return containerBillList;
//    }
//


    //导出客户拖车账单
    @Override
    public JsonResult exportsCustomerContainerBill(ContainerBill containerBill, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        String fileName = "containerBill.xlsx";
        File file = new File(getUploadDir(), fileName);
        if (file.exists()) {
            file.delete();
        }
        containerBill.setDate1(containerBill.getDate1().substring(0, 10) + " 00:00");
        containerBill.setDate2(containerBill.getDate2().substring(0, 10) + " 23:59");
        List<ContainerBill> containerBillList = getContainerList(containerBill, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            List<String> feeNames = containerBillFeeBiz.feeNamesForCustomer(containerBill, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsCustomerContainerBillExcel(containerBillList, feeNames, containerBill, fileName);
                FileServerUtil.preview(file, true, fileName, null, null, response, request);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return JsonResult.ok("文件已导出");
    }

    public List<ContainerBill> getContainerList(ContainerBill containerBill, User loginUser) {
        ContainerBillParam containerBillParam = new ContainerBillParam();
        containerBillParam.setFactoryPackDateSearch1(containerBill.getDate1());
        containerBillParam.setFactoryPackDateSearch2(containerBill.getDate2());
        containerBillParam.setCustomerAbbreviation(containerBill.getCustomerAbbreviation());
        containerBillParam.setAutocompletevalue(containerBill.getCustomer());
        List<ContainerBill> containerBillList = containerBillBiz.listForCustomerFeeList(containerBillParam, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            for (int i = 0; i < containerBillList.size(); i++) {
                ContainerBill containerBillTemp = containerBillList.get(i);
                containerBillParam = new ContainerBillParam();
//                if (StringUtil.isNotBlank(containerBill.getType())) {
//                    containerBillParam.setType(containerBill.getType());//导出应收应付
//                }
                if (StringUtil.isNotBlank(containerBillTemp.getId())) {
                    containerBillParam.setId(containerBillTemp.getId());
                }
                if (StringUtil.isNotBlank(containerBillTemp.getApplyId())) {
                    containerBillParam.setApplyId(containerBillTemp.getApplyId());
                }
                if (StringUtil.isNotBlank(containerBill.getCustomerAbbreviation())) {
                    containerBillParam.setCustomerAbbreviation(containerBill.getCustomerAbbreviation());
                }
                List<ContainerBillFee> containerBillFeeList = containerBillFeeBiz.listForCustomerExport(containerBillParam, loginUser);
                containerBillTemp.setContainerBillFeeList(containerBillFeeList);
            }
        }
        return containerBillList;
    }

    //导出客户散货账单
    @Override
    public JsonResult exportsCustomerOceanBulkCargo(OceanBulkCargo oceanBulkCargo, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        String fileName = "oceanBulkCargo.xlsx";
        File file = new File(getUploadDir(), fileName);
        if (file.exists()) {
            file.delete();
        }
        oceanBulkCargo.setDate1(oceanBulkCargo.getDate1().substring(0, 10) + " 00:00");
        oceanBulkCargo.setDate2(oceanBulkCargo.getDate2().substring(0, 10) + " 23:59");
        List<OceanBulkCargo> oceanBulkCargoList = getOceanBulkCargoList(oceanBulkCargo, loginUser);
        if (oceanBulkCargoList != null && oceanBulkCargoList.size() > 0) {
            List<String> feeNames = oceanBulkCargoFeeBiz.feeNamesForCustomer(oceanBulkCargo, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsCustomerOceanBulkCargoExcel(oceanBulkCargoList, feeNames, oceanBulkCargo,fileName);
                FileServerUtil.preview(file, true, fileName, null, null, response, request);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return JsonResult.ok("文件已导出");
    }

    private List<OceanBulkCargo> getOceanBulkCargoList(OceanBulkCargo oceanBulkCargo, User loginUser) {
        OceanBulkCargoParam oceanBulkCargoParam = new OceanBulkCargoParam();
        oceanBulkCargoParam.setFactoryPackDateSearch1(oceanBulkCargo.getDate1());
        oceanBulkCargoParam.setFactoryPackDateSearch2(oceanBulkCargo.getDate2());
        oceanBulkCargoParam.setBillCode(oceanBulkCargo.getBillCode());
        oceanBulkCargoParam.setCustomerAbbreviation(oceanBulkCargo.getCustomerAbbreviation());
        oceanBulkCargoParam.setAutocompletevalue(oceanBulkCargo.getCustomer());
        List<OceanBulkCargo> oceanBulkCargoList = oceanBulkCargoBiz.listForCustomerFeeList(oceanBulkCargoParam, loginUser);
        if (oceanBulkCargoList != null && oceanBulkCargoList.size() > 0) {
            for (int i = 0; i < oceanBulkCargoList.size(); i++) {
                OceanBulkCargo oceanBulkCargoTemp = oceanBulkCargoList.get(i);
                oceanBulkCargoParam = new OceanBulkCargoParam();
                if (StringUtil.isNotBlank(oceanBulkCargo.getType())) {
                    oceanBulkCargoParam.setType(oceanBulkCargo.getType());//导出应收应付
                }
                if (StringUtil.isNotBlank(oceanBulkCargoTemp.getId())) {
                    oceanBulkCargoParam.setId(oceanBulkCargoTemp.getId());
                }
                if (StringUtil.isNotBlank(oceanBulkCargo.getCustomerAbbreviation())) {
                    oceanBulkCargoParam.setCustomerAbbreviation(oceanBulkCargo.getCustomerAbbreviation());
                }
                try {
                    List<OceanBulkCargoFee> oceanBulkCargoFeeList = oceanBulkCargoFeeBiz.listForCustomerExport(oceanBulkCargoParam, loginUser);
                    oceanBulkCargoTemp.setOceanBulkCargoFees(oceanBulkCargoFeeList);
                } catch (Exception e) {
                }
            }
        }
        return oceanBulkCargoList;
    }


    public void exportsCustomerContainerBillExcel(List<ContainerBill> containerBillList, List<String> feeNames, ContainerBill containerBill, String fileName) throws Exception {

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String to = containerBill.getCustomer();

        xssfCell.setCellValue("To：" + to); //设置单元格内容

        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(10 + feeNames.size() - 4); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(containerBill.getDate2())) {
            date2 = " 到 " + containerBill.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期：" + containerBill.getDate1().substring(0, 10) + date2); //设置单元格内容

        List<String> headRow = CollUtil.newArrayList("序号", "日期", "客户简称", "提单号", "箱型", "箱号", "工厂地址",
                "提箱场站", "还箱场站", "车牌号");
        headRow.addAll(feeNames);
        headRow.add("小计");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
        xssfSheet.createFreezePane(0, 4, 0, 0);

        String[] letters = new String[]{"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR",
                "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ",
                "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT"};

        //把List里面的数据写到excel中
        for (int i = 0; i < containerBillList.size(); i++) {
            ContainerBill bill = containerBillList.get(i);
            List<ContainerBillFee> billFees = bill.getContainerBillFeeList();

            xssfRow = xssfSheet.createRow(i + 4);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i + 1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容

            String add = "";
            if (bill.getFactoryAddress() != null && bill.getFactoryAddress().length() > 0) {
                JSONArray jsonArray = JSONArray.fromObject(bill.getFactoryAddress());
                for (int k = 0; k < jsonArray.size(); k++) {
                    if (k != 0) {
                        add += ", \n";
                    }
                    JSONObject jsonObject = jsonArray.getJSONObject(k);
                    add += jsonObject.getString("factoryAddress").replaceAll("\t", "");
                }
            }

            List<String> row = CollUtil.newArrayList((i + 1) + "", DateUtil.formatDate(DateUtil.parseDate(bill.getFactoryPackDate() + ":00"), "MM-dd"),
                    bill.getCustomer(), bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), add,
                    bill.getUpYard(), bill.getArrivalPort() == "null" ? "" : bill.getArrivalPort(), bill.getCarNo());
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            float temp_ret = 0;
            for (int t = 0; t < feeNames.size(); t++) {
                String feename = feeNames.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j++) {
                    ContainerBillFee bf = billFees.get(j);
                    if (bf.getFeeName().equals(feename)) {
                        if (Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM(" + letters[0] + (i + 5) + ":" + letters[feeNames.size() - 1] + (i + 5) + ")");

            setRow(xssfRow, row, cellStyle, ",10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,");
        }

        xssfRow = xssfSheet.createRow(4 + containerBillList.size());
//        List<String> row = new ArrayList<>();
//        row.add("");row.add("");row.add("");row.add("");row.add("");
//        row.add("");row.add("");row.add("");row.add("");
//        setRowNoValue(xssfRow,row, cellStyle,"");
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容


        for (int i = 0; i <= feeNames.size(); i++) {
            xssfCell = xssfRow.createCell(10 + i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM(" + letters[i] + "5:" + letters[i] + (containerBillList.size() + 4) + ")"); //设置单元格内容
        }

        // 合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 10 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(1, 1, 0, 10 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列

        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 0, 10 + feeNames.size() - 5); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 10 + feeNames.size() - 4, 10 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(4 + containerBillList.size(), 4 + containerBillList.size(), 0, 9); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(getUploadDir() + fileName));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();
    }



    public void exportsCustomerOceanBulkCargoExcel(List<OceanBulkCargo> oceanBulkCargoList, List<String> feeNames, OceanBulkCargo oceanBulkCargo, String fileName) throws Exception {

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String to = oceanBulkCargo.getCustomer();

        xssfCell.setCellValue("To：" + to); //设置单元格内容

        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(5 + feeNames.size() - 3); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(oceanBulkCargo.getDate2())) {
            date2 = " 到 " + oceanBulkCargo.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期：" + oceanBulkCargo.getDate1().substring(0, 10) + date2); //设置单元格内容

        List<String> headRow = CollUtil.newArrayList("序号", "日期", "客户简称", "单号", "客户单号");
        headRow.addAll(feeNames);
        headRow.add("小计");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
        xssfSheet.createFreezePane(0, 4, 0, 0);

        String[] letters = new String[]{"F", "G", "H", "I", "J","K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR",
                "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ",
                "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT"};

        //把List里面的数据写到excel中
        for (int i = 0; i < oceanBulkCargoList.size(); i++) {
            OceanBulkCargo oceanBulkCargo1 = oceanBulkCargoList.get(i);
            List<OceanBulkCargoFee> oceanBulkCargoFeeList = oceanBulkCargo1.getOceanBulkCargoFees();

            xssfRow = xssfSheet.createRow(i + 4);

            List<String> row = CollUtil.newArrayList((i + 1) + "", oceanBulkCargo1.getCreateTime().substring(0,10),
                    oceanBulkCargo1.getCustomer(), oceanBulkCargo1.getBillCode(), oceanBulkCargo1.getCustomerCode()==null?" ":oceanBulkCargo1.getCustomerCode()
                   );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            float temp_ret = 0;
            for (int t = 0; t < feeNames.size(); t++) {
                String feename = feeNames.get(t);
                temp_ret = 0;
                for (int j = 0; j < oceanBulkCargoFeeList.size(); j++) {
                    OceanBulkCargoFee oceanBulkCargoFee = oceanBulkCargoFeeList.get(j);
                    if (oceanBulkCargoFee.getFeeName().equals(feename)) {
                        if (Utils.isNotEmpty(oceanBulkCargoFee.getPrice())) {
                            temp_ret += Float.parseFloat(oceanBulkCargoFee.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM(" + letters[0] + (i + 5) + ":" + letters[feeNames.size() - 1] + (i + 5) + ")");

            setRow(xssfRow, row, cellStyle, "4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,");
        }

        xssfRow = xssfSheet.createRow(4 + oceanBulkCargoList.size());
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容


        for (int i = 0; i <= feeNames.size(); i++) {
            xssfCell = xssfRow.createCell(5 + i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM(" + letters[i] + "5:" + letters[i] + (oceanBulkCargoList.size() + 4) + ")"); //设置单元格内容
        }

        // 合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 5 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(1, 1, 0, 5 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列

        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 0, 5 + feeNames.size() - 4); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 5 + feeNames.size() - 3, 5 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(4 + oceanBulkCargoList.size(), 4 + oceanBulkCargoList.size(), 0, 4); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(getUploadDir() + fileName));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();
    }

    //应收应付分类导出财务报表
    @Override
    public void exportsCustomerOceanBulkCargo2(OceanBulkCargo oceanBulkCargo, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        oceanBulkCargo.setDate1(oceanBulkCargo.getDate1().substring(0, 10) + " 00:00");
        oceanBulkCargo.setDate2(oceanBulkCargo.getDate2().substring(0, 10) + " 23:59");
        oceanBulkCargo.setType("应收");
        List<OceanBulkCargo> oceanBulkCargoList = getOceanBulkCargoList2(oceanBulkCargo, loginUser);
        if (oceanBulkCargoList != null && oceanBulkCargoList.size() > 0) {
            List<String> feeNames = oceanBulkCargoFeeBiz.feeNamesForCustomer(oceanBulkCargo, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsCustomerOceanBulkCargoExcel2(oceanBulkCargoList, feeNames, oceanBulkCargo);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private List<OceanBulkCargo> getOceanBulkCargoList2(OceanBulkCargo oceanBulkCargo, User loginUser) {
        OceanBulkCargoParam oceanBulkCargoParam = new OceanBulkCargoParam();
        oceanBulkCargoParam.setFactoryPackDateSearch1(oceanBulkCargo.getDate1());
        oceanBulkCargoParam.setFactoryPackDateSearch2(oceanBulkCargo.getDate2());
        oceanBulkCargoParam.setBillCode(oceanBulkCargo.getBillCode());
        oceanBulkCargoParam.setType("应收");
        oceanBulkCargoParam.setCustomerAbbreviation(oceanBulkCargo.getCustomerAbbreviation());
        oceanBulkCargoParam.setAutocompletevalue(oceanBulkCargo.getCustomer());
        List<OceanBulkCargo> oceanBulkCargoList = oceanBulkCargoBiz.listForCustomerFeeList(oceanBulkCargoParam, loginUser);
        if (oceanBulkCargoList != null && oceanBulkCargoList.size() > 0) {
            for (int i = 0; i < oceanBulkCargoList.size(); i++) {
                OceanBulkCargo oceanBulkCargoTemp = oceanBulkCargoList.get(i);
                oceanBulkCargoParam = new OceanBulkCargoParam();
                if (StringUtil.isNotBlank(oceanBulkCargo.getType())) {
                    oceanBulkCargoParam.setType(oceanBulkCargo.getType());//导出应收应付
                }
                if (StringUtil.isNotBlank(oceanBulkCargoTemp.getId())) {
                    oceanBulkCargoParam.setId(oceanBulkCargoTemp.getId());
                }
                if (StringUtil.isNotBlank(oceanBulkCargo.getCustomerAbbreviation())) {
                    oceanBulkCargoParam.setCustomerAbbreviation(oceanBulkCargo.getCustomerAbbreviation());
                }
                try {
                    List<OceanBulkCargoFee> oceanBulkCargoFeeList = oceanBulkCargoFeeBiz.listForCustomerExport(oceanBulkCargoParam, loginUser);
                    oceanBulkCargoTemp.setOceanBulkCargoFees(oceanBulkCargoFeeList);
                } catch (Exception e) {
                }
            }
        }
        return oceanBulkCargoList;
    }


    public void exportsCustomerOceanBulkCargoExcel2(List<OceanBulkCargo> oceanBulkCargoList, List<String> feeNames, OceanBulkCargo oceanBulkCargo) throws Exception {

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html

        // 根据模板创建excel工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(getUploadDir() + "oceanBulkCargo_customerCurrency.xlsx");

        // 获取创建的工作簿第一页
        XSSFSheet sheet = xssfWorkbook.getSheet("Sheet1");
        sheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建列，即单元格Cell
        XSSFRow xssfRow =  null;
        //创建列，即单元格Cell
        XSSFCell xssfCell = null;

        //把Excel模板文件里的数据清空
        setBlankExcel(xssfWorkbook, sheet,oceanBulkCargoList);
        // 获取第一行
        xssfRow = sheet.getRow(5);
        // 先定义创建excel表头
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue("To:" + oceanBulkCargo.getCustomer());

        xssfRow = sheet.getRow(6);
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue("提单号:" + oceanBulkCargo.getBillCode());
        xssfRow = sheet.getRow(7);
        xssfCell = xssfRow.getCell(0); //创建单元格
        xssfCell.setCellValue("客户:" + oceanBulkCargo.getCustomer());

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(false); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 11); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);


//        int count = 11;
//        OceanBulkCargoFee oceanBulkCargoFee2 = null;
//        float sum = 0, usd = 0, cny = 0, myr = 0;
//        if (oceanBulkCargoFees != null && oceanBulkCargoFees.size() > 0) {
//            count += oceanBulkCargoFees.size();
//            for (int i = 0; i < oceanBulkCargoFees.size(); i ++) {
//                oceanBulkCargoFee2 = oceanBulkCargoFees.get(i);
//                xssfRow = sheet.getRow(11+i);
//                xssfCell = xssfRow.getCell(0); //创建单元格
//                xssfCell.setCellValue(oceanBulkCargoFee2.getFeeName());
//                xssfCell = xssfRow.getCell(1); //创建单元格
//                xssfCell.setCellValue(oceanBulkCargoFee2.getPrice());
//                xssfCell = xssfRow.getCell(2); //创建单元格
//                xssfCell.setCellValue(1);
//                try {
//                    if (oceanBulkCargoFee2.getCurrency().equals("USD")) {
//                        xssfCell = xssfRow.getCell(3); //创建单元格
//                        xssfCell.setCellType(CellType.NUMERIC);
//                        xssfCell.setCellValue(Double.valueOf(oceanBulkCargoFee2.getPrice()));
//                        usd += Double.valueOf(oceanBulkCargoFee2.getPrice());
//                    }
//                    if (oceanBulkCargoFee2.getCurrency().equals("CNY")) {
//                        xssfCell = xssfRow.getCell(4); //创建单元格
//                        xssfCell.setCellType(CellType.NUMERIC);
//                        xssfCell.setCellValue(Double.valueOf(oceanBulkCargoFee2.getPrice()));
//                        cny += Double.valueOf(oceanBulkCargoFee2.getPrice());
//                    }
//                    if (oceanBulkCargoFee2.getCurrency().equals("MYR")) {
//                        xssfCell = xssfRow.getCell(5); //创建单元格
//                        xssfCell.setCellType(CellType.NUMERIC);
//                        xssfCell.setCellValue(Double.valueOf(oceanBulkCargoFee2.getPrice()));
//                        myr += Double.valueOf(oceanBulkCargoFee2.getPrice());
//                    }
//                    sum += Double.valueOf(oceanBulkCargoFee2.getPrice());
//                } catch (Exception e){
//
//                }
//                xssfCell = xssfRow.getCell(6); //创建单元格
//                xssfCell.setCellValue(oceanBulkCargoFee2.getRemark());
//            }
//        }
//
//
//        fontStyle.setBold(true); // 加粗
//        cellStyle.setFont(fontStyle);
//        xssfRow = sheet.getRow(count);
//        xssfRow.setHeightInPoints(30); // 设置行高为30个点
//        xssfCell = xssfRow.getCell(0); //创建单元格
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("应收USD:"+ String.valueOf(usd).replaceAll("\\.0*$", ""));
//        xssfCell = xssfRow.getCell(1); //创建单元格
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("应收MYR:"+ String.valueOf(myr).replaceAll("\\.0*$", ""));
//        xssfCell = xssfRow.getCell(6); //创建单元格
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("应收RMB:"+ String.valueOf(cny).replaceAll("\\.0*$", ""));
//
//        xssfRow = sheet.getRow(count+1);
//        xssfRow.setHeightInPoints(80); // 设置行高为30个点
//        xssfCell = xssfRow.getCell(0); //创建单元格
//        cellStyle.setAlignment(HorizontalAlignment.LEFT);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("以上为贵司代理运输所产生的费用，请在2天内确认完毕，逾期视作默认，并3天内付清费用，感谢配合。");
//
//        xssfRow = sheet.getRow(count+2);
//        xssfRow.setHeightInPoints(300); // 设置行高为30个点
//        xssfCell = xssfRow.getCell(0); //创建单元格
//        cellStyle.setWrapText(true);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("个人账号：\n" +
//                "户名：蔡寒峰\n" +
//                "账号：6228480318392094476\n" +
//                "开户行：中国农业银行股份有限公司宁波柴桥支行\n" +
//                "\n" +
//                "公司账号：\n" +
//                "账户名：宁波甩手掌柜供应链有限公司\n" +
//                "税号：91330201MA2H47KE90\n" +
//                "开户行：中国农业银行股份有限公司宁波潘火支行\n" +
//                "账号：(RMB)  39446001040006801\n" +
//                "开户行：宁波银行保险科技产业园支行\n" +
//                "银行账号：(RMB)3206 0122 0000 1471 2\n" +
//                "银行账号：（USD）3206 2025 0000 0012 6\n" +
//                "SWIFT CODE：BKNBCN2NXXX\n" +
//                "发票寄到浙江省宁波市鄞州区 下应街道 宁波保险科技产业园 甩手掌柜2201-3，黄小姐 18892631673");
//
//
//        // 合并单元格
//        CellRangeAddress cra = new CellRangeAddress(count+1, count+1, 0, 0 + 6); // 起始行, 终止行, 起始列, 终止列
//        sheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(count+2, count+2, 0, 0 + 6); // 起始行, 终止行, 起始列, 终止列
//        sheet.addMergedRegion(cra);

//        try {
//
//            // 创建Excel文件的输入流对象
//            OutputStream os = response.getOutputStream();
//            // 这时候把创建好的excel写入到输出流
//            xssfWorkbook.write(os);
//            // 关闭流
//            os.flush();
//            os.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//            response.setContentType("text/html; charset=UTF-8");
//            response.getWriter().println("没有对应的数据哦,请再次查询!");
//        }



















//
//        //创建工作簿
//        XSSFWorkbook xssfWorkbook = null;
//        xssfWorkbook = new XSSFWorkbook();
//
//        //创建工作表
//        XSSFSheet xssfSheet;
//        xssfSheet = xssfWorkbook.createSheet();
//        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题
//
//        //创建行
//        XSSFRow xssfRow;
//
//        //创建列，即单元格Cell
//        XSSFCell xssfCell;
//
//        xssfRow = xssfSheet.createRow(0);
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(0); //创建单元格*
//
//        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 18); // 大小
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        cellStyle.setAlignment(HorizontalAlignment.CENTER);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容
//
//        xssfRow = xssfSheet.createRow(1);
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(0); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 12); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.CENTER);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容
//
//        xssfRow = xssfSheet.createRow(2);
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(0); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 12); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.LEFT);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        xssfCell.setCellStyle(cellStyle);
//        String to = oceanBulkCargo.getCustomer();
//
//        xssfCell.setCellValue("To：" + to); //设置单元格内容
//
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(5 + feeNames.size() - 3); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 10); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        xssfCell.setCellStyle(cellStyle);
//        String date2 = "";
//        if (Utils.isNotEmpty(oceanBulkCargo.getDate2())) {
//            date2 = " 到 " + oceanBulkCargo.getDate2().substring(0, 10);
//        }
//        xssfCell.setCellValue("账期：" + oceanBulkCargo.getDate1().substring(0, 10) + date2); //设置单元格内容
//
//        List<String> headRow = CollUtil.newArrayList("序号", "日期", "客户简称", "单号", "客户单号");
//        headRow.addAll(feeNames);
//        headRow.add("小计");
//        xssfRow = xssfSheet.createRow(3);
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 10); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setBorderBottom(BorderStyle.THIN);
//        cellStyle.setBorderLeft(BorderStyle.THIN);
//        cellStyle.setBorderRight(BorderStyle.THIN);
//        cellStyle.setBorderTop(BorderStyle.THIN);
//        cellStyle.setAlignment(HorizontalAlignment.CENTER);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane(0, 4, 0, 0);
//
//        String[] letters = new String[]{"F", "G", "H", "I", "J","K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
//                "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR",
//                "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ",
//                "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT"};
//
//        //把List里面的数据写到excel中
//        for (int i = 0; i < oceanBulkCargoList.size(); i++) {
//            OceanBulkCargo oceanBulkCargo1 = oceanBulkCargoList.get(i);
//            List<OceanBulkCargoFee> oceanBulkCargoFeeList = oceanBulkCargo1.getOceanBulkCargoFees();
//
//            xssfRow = xssfSheet.createRow(i + 4);
//
//            List<String> row = CollUtil.newArrayList((i + 1) + "", oceanBulkCargo1.getCreateTime().substring(0,10),
//                    oceanBulkCargo1.getCustomer(), oceanBulkCargo1.getBillCode(), oceanBulkCargo1.getCustomerCode()==null?" ":oceanBulkCargo1.getCustomerCode()
//            );
//            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//            fontStyle = xssfWorkbook.createFont(); // 字体样式
//            fontStyle.setBold(false); // 加粗
//            fontStyle.setFontName("宋体"); // 字体
//            fontStyle.setFontHeightInPoints((short) 10); // 大小
//            // 将字体样式添加到单元格样式中
//            cellStyle.setFont(fontStyle);
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
//            cellStyle.setBorderBottom(BorderStyle.THIN);
//            cellStyle.setBorderLeft(BorderStyle.THIN);
//            cellStyle.setBorderRight(BorderStyle.THIN);
//            cellStyle.setBorderTop(BorderStyle.THIN);
//
//            float temp_ret = 0;
//            for (int t = 0; t < feeNames.size(); t++) {
//                String feename = feeNames.get(t);
//                temp_ret = 0;
//                for (int j = 0; j < oceanBulkCargoFeeList.size(); j++) {
//                    OceanBulkCargoFee oceanBulkCargoFee = oceanBulkCargoFeeList.get(j);
//                    if (oceanBulkCargoFee.getFeeName().equals(feename)) {
//                        if (Utils.isNotEmpty(oceanBulkCargoFee.getPrice())) {
//                            temp_ret += Float.parseFloat(oceanBulkCargoFee.getPrice());
//                        }
//                    }
//                }
//                row.add(temp_ret + "");
//            }
//            row.add("function::SUM(" + letters[0] + (i + 5) + ":" + letters[feeNames.size() - 1] + (i + 5) + ")");
//
//            setRow(xssfRow, row, cellStyle, "4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,");
//        }
//
//        xssfRow = xssfSheet.createRow(4 + oceanBulkCargoList.size());
//        //创建每个单元格Cell，即列的数据
//        xssfCell = xssfRow.createCell(0); //创建单元格
//        fontStyle = xssfWorkbook.createFont(); // 字体样式
//        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("宋体"); // 字体
//        fontStyle.setFontHeightInPoints((short) 10); // 大小
//        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//        // 将字体样式添加到单元格样式中
//        cellStyle.setFont(fontStyle);
//        cellStyle.setBorderBottom(BorderStyle.THIN);
//        cellStyle.setBorderLeft(BorderStyle.THIN);
//        cellStyle.setBorderRight(BorderStyle.THIN);
//        cellStyle.setBorderTop(BorderStyle.THIN);
//        xssfCell.setCellStyle(cellStyle);
//        xssfCell.setCellValue("合计:"); //设置单元格内容
//
//
//        for (int i = 0; i <= feeNames.size(); i++) {
//            xssfCell = xssfRow.createCell(5 + i); //创建单元格
//            fontStyle = xssfWorkbook.createFont(); // 字体样式
//            fontStyle.setFontName("宋体"); // 字体
//            fontStyle.setFontHeightInPoints((short) 10); // 大小
//            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
//            // 将字体样式添加到单元格样式中
//            cellStyle.setFont(fontStyle);
//            cellStyle.setBorderBottom(BorderStyle.THIN);
//            cellStyle.setBorderLeft(BorderStyle.THIN);
//            cellStyle.setBorderRight(BorderStyle.THIN);
//            cellStyle.setBorderTop(BorderStyle.THIN);
//            xssfCell.setCellStyle(cellStyle);
//            xssfCell.setCellType(CellType.NUMERIC);
//            xssfCell.setCellFormula("SUM(" + letters[i] + "5:" + letters[i] + (oceanBulkCargoList.size() + 4) + ")"); //设置单元格内容
//        }
//
//        // 合并单元格
//        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 5 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(1, 1, 0, 5 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
//
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(2, 2, 0, 5 + feeNames.size() - 4); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(2, 2, 5 + feeNames.size() - 3, 5 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
//        xssfSheet.addMergedRegion(cra);
//        cra = new CellRangeAddress(4 + oceanBulkCargoList.size(), 4 + oceanBulkCargoList.size(), 0, 4); // 起始行, 终止行, 起始列, 终止列
//        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
//        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
//        xssfSheet.addMergedRegion(cra);
//
//        OutputStream os = new FileOutputStream(new File(getUploadDir() + fileName));
//        //用输出流写到excel
//        xssfWorkbook.write(os);
//        os.flush();
//        os.close();
    }


    public void setBlankExcel(XSSFWorkbook xssfWorkbook, XSSFSheet sheet,
                              List<OceanBulkCargo> oceanBulkCargoList){
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(false); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 11); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        int count = 0;
        List<List<OceanBulkCargoFee>> name  = oceanBulkCargoList.stream()
                .map((obo)-> obo.getOceanBulkCargoFees()).collect(Collectors.toList());
        if (Utils.isNotEmpty(oceanBulkCargoList)) {
//            count += oceanBulkCargoFees.size();
        }
        XSSFRow xssfRow = null;
        XSSFCell xssfCell = null;
        for (int i = 11; i < 11+ count; i++) {
            xssfRow = sheet.getRow(i);
            xssfCell = xssfRow.getCell(0); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(1); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(2); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(3); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(4); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(5); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
            xssfCell = xssfRow.getCell(6); //创建单元格
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellValue("" );
        }

    }


    public void setRow(XSSFRow xssfRow, List<String> row, XSSFCellStyle cellStyle, String changeNum) {
        //创建每个单元格Cell，即列的数据
        if (Utils.isNotEmpty(row)) {
            for (int i = 0; i < row.size(); i++) {
                XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
                String str = row.get(i);
                if (str == null) {
                    str = "";
                }
                //https://blog.csdn.net/weixin_29100399/article/details/114203311
                if (str.indexOf("function::") != -1) {
                    xssfCell.setCellFormula(str.substring(10, str.length())); //设置单元格内容
                } else if (changeNum.indexOf("," + i + ",") != -1) {
                    xssfCell.setCellType(CellType.NUMERIC);
                    if (Utils.isNotEmpty(row.get(i))) {
                        xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
                    } else {
                        xssfCell.setCellValue(0);
                    }
                } else {
                    xssfCell.setCellValue(row.get(i)); //设置单元格内容
                }
                if (cellStyle != null) {
                    xssfCell.setCellStyle(cellStyle);
                }
            }
        }
    }


    //导出车队账单
    @Transactional
    @Override
    public JsonResult exportsCarTeam(ContainerBill containerBill, User loginUser, HttpServletResponse response, HttpServletRequest request) throws Exception {
        File file = new File(getUploadDir(), "driver.xlsx");
        if (file.exists()) {
            file.delete();
        }
        containerBill.setDate1(containerBill.getDate1().substring(0, 10) + " 00:00");
        containerBill.setDate2(containerBill.getDate2().substring(0, 10) + " 23:59");
        List<ContainerBill> containerBillList = getCarteamContainerList(containerBill, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            List<String> feeNames = containerBillFeeBiz.feeNames(containerBill, loginUser);
            if (feeNames != null && feeNames.size() > 0) {
                exportsCustomerExcel(containerBillList, feeNames, containerBill);
                FileServerUtil.preview(file, true, "driver.xlsx", null, null, response, request);
            } else {
                try {
                    response.setContentType("text/html; charset=UTF-8");
                    response.getWriter().println("没有对应的数据哦,请再次查询!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return JsonResult.ok("文件已导出");
    }


    public List<ContainerBill> getCarteamContainerList(ContainerBill containerBill, User loginUser) {
        ContainerBillParam containerBillParam = new ContainerBillParam();
        containerBillParam.setFactoryPackDateSearch1(containerBill.getDate1());
        containerBillParam.setFactoryPackDateSearch2(containerBill.getDate2());
        containerBillParam.setCarTeamId(containerBill.getCarTeamId());
        List<ContainerBill> containerBillList = containerBillBiz.list(containerBillParam, loginUser);
        if (containerBillList != null && containerBillList.size() > 0) {
            for (int i = 0; i < containerBillList.size(); i++) {
                ContainerBill containerBillTemp = containerBillList.get(i);
                containerBillParam = new ContainerBillParam();
                if (StringUtil.isNotBlank(containerBill.getType())) {
                    containerBillParam.setType(containerBill.getType());//导出应收应付
                }
                if (StringUtil.isNotBlank(containerBillTemp.getId())) {
                    containerBillParam.setId(containerBillTemp.getId());
                }
                if (StringUtil.isNotBlank(containerBillTemp.getApplyId())) {
                    containerBillParam.setApplyId(containerBillTemp.getApplyId());
                }
                if (StringUtil.isNotBlank(containerBill.getCarTeamAbbreviation())) {
                    containerBillParam.setCarTeamAbbreviation(containerBill.getCarTeamAbbreviation());
                }
                List<ContainerBillFee> containerBillFeeList = containerBillFeeBiz.listForExport(containerBillParam, loginUser);
                containerBillTemp.setContainerBillFeeList(containerBillFeeList);
            }
        }
        return containerBillList;
    }


    public void exportsCustomerExcel(List<ContainerBill> containerBillList, List<String> feeNames, ContainerBill containerBill) throws Exception {

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String to = containerBill.getCarTeam();
        xssfCell.setCellValue("To：" + to); //设置单元格内容

        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(9 + feeNames.size() - 4); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(containerBill.getDate2())) {
            date2 = " 到 " + containerBill.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期：" + containerBill.getDate1().substring(0, 10) + date2); //设置单元格内容

        List<String> headRow = CollUtil.newArrayList("序号", "日期", "提单号", "箱型", "箱号", "工厂地址",
                "提箱场站", "还箱场站", "车牌号");
        headRow.addAll(feeNames);
        headRow.add("小计");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
        xssfSheet.createFreezePane(0, 4, 0, 0);

        String[] letters = new String[]{"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR",
                "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ",
                "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT"};

        //把List里面的数据写到excel中
        for (int i = 0; i < containerBillList.size(); i++) {
            ContainerBill bill = containerBillList.get(i);
            List<ContainerBillFee> billFees = bill.getContainerBillFeeList();

            xssfRow = xssfSheet.createRow(i + 4);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i + 1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容

            String add = "";
            if (bill.getFactoryAddress() != null && bill.getFactoryAddress().length() > 0) {
                JSONArray jsonArray = JSONArray.fromObject(bill.getFactoryAddress());
                for (int k = 0; k < jsonArray.size(); k++) {
                    if (k != 0) {
                        add += ", \n";
                    }
                    JSONObject jsonObject = jsonArray.getJSONObject(k);
                    add += jsonObject.getString("factoryAddress").replaceAll("\t", "");
                }
            }
            List<String> row = CollUtil.newArrayList((i + 1) + "", DateUtil.formatDate(DateUtil.parseDate(bill.getFactoryPackDate() + ":00"), "MM-dd"),
                    bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), add,
                    bill.getUpYard(), bill.getArrivalPort() == "null" ? "" : bill.getArrivalPort(), bill.getCarNo());
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            float temp_ret = 0;
            for (int t = 0; t < feeNames.size(); t++) {
                String feename = feeNames.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j++) {
                    ContainerBillFee bf = billFees.get(j);
                    if (bf.getFeeName().equals(feename)) {
                        if (Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM(" + letters[0] + (i + 5) + ":" + letters[feeNames.size() - 1] + (i + 5) + ")");

            setRow(xssfRow, row, cellStyle, ",9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,");
        }

        xssfRow = xssfSheet.createRow(4 + containerBillList.size());
//        List<String> row = new ArrayList<>();
//        row.add("");row.add("");row.add("");row.add("");row.add("");
//        row.add("");row.add("");row.add("");row.add("");
//        setRowNoValue(xssfRow,row, cellStyle,"");
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容


        for (int i = 0; i <= feeNames.size(); i++) {
            xssfCell = xssfRow.createCell(9 + i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM(" + letters[i] + "5:" + letters[i] + (containerBillList.size() + 4) + ")"); //设置单元格内容
        }

        // 合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 9 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(1, 1, 0, 9 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列

        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 0, 9 + feeNames.size() - 5); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(2, 2, 9 + feeNames.size() - 4, 9 + feeNames.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra = new CellRangeAddress(4 + containerBillList.size(), 4 + containerBillList.size(), 0, 8); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(getUploadDir() + "driver.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();
    }


    @Resource
    private ConfigProperties config;

    /**
     * 文件上传基目录
     */
    private String getUploadBaseDir() {
        return File.listRoots()[config.getUploadLocation()].getAbsolutePath()
                .replace("\\", "/") + "/upload/";
    }

    /**
     * 文件上传位置
     */
    private String getUploadDir() {
        return getUploadBaseDir() + "file/";
    }
}
